﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace ConfigurationManager.DataAccess
{
    public class InteligenceModule : BaseResourceAccess
    {
        public InteligenceModule()
            : base()
        {
        }

        public List<Model.InteligenceModule> Retrieve()
        {
            OracleCommand command = this.OracleConnection.CreateCommand();
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COD_MOD_INTL, NOME_MOD_INTL, NOME_ASSBLY_MOD_INTL, DESC_MOD_INTL, NOME_CLAS_MOD_INTL ");
            sql.Append("FROM TFWDMOD_INTL_GDOR_PROS ");

            command.CommandText = sql.ToString();
            command.CommandType = System.Data.CommandType.Text;

            List<Model.InteligenceModule> ret = new List<ConfigurationManager.Model.InteligenceModule>();

            try
            {
                this.OracleConnection.Open();

                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Model.InteligenceModule inteligenceModule = new Model.InteligenceModule();
                        inteligenceModule.Id = reader.GetInt32(0);
                        inteligenceModule.Name = reader.GetString(1);
                        inteligenceModule.Assembly = reader.GetString(2);
                        inteligenceModule.Description = reader.GetString(3);
                        inteligenceModule.Class = reader.GetString(4);
                        ret.Add(inteligenceModule);
                    }
                }
                return ret;
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public void Save(Model.InteligenceModule inteligenceModule)
        {
            StringBuilder sql = new StringBuilder();
            OracleCommand lockCommand = null;
            OracleTransaction trans = null;
            try
            {
                this.OracleConnection.Open();
                trans = this.OracleConnection.BeginTransaction();

                //lock da tabela para evitar problemas na geracao do id
                lockCommand = this.OracleConnection.CreateCommand();
                lockCommand.Transaction = trans;
                lockCommand.CommandText = "SELECT 1 FROM TFWDMOD_INTL_GDOR_PROS FOR UPDATE";
                lockCommand.CommandType = System.Data.CommandType.Text;
                lockCommand.ExecuteNonQuery();

                if (inteligenceModule.Id > 0)
                {
                    sql.Append("UPDATE TFWDMOD_INTL_GDOR_PROS ");
                    sql.Append("SET NOME_MOD_INTL =:NOME_MOD_INTL, ");
                    sql.Append("DESC_MOD_INTL =:DESC_MOD_INTL, ");
                    sql.Append("NOME_ASSBLY_MOD_INTL =:NOME_ASSBLY_MOD_INTL, ");
                    sql.Append("NOME_CLAS_MOD_INTL =:NOME_CLAS_MOD_INTL ");
                    sql.Append("WHERE COD_MOD_INTL =:COD_MOD_INTL ");
                }
                else
                {
                    inteligenceModule.Id = this.GetNextId(trans);
                    sql.Append("INSERT INTO TFWDMOD_INTL_GDOR_PROS ");
                    sql.Append("(COD_MOD_INTL, NOME_MOD_INTL, DESC_MOD_INTL, NOME_ASSBLY_MOD_INTL, NOME_CLAS_MOD_INTL) ");
                    sql.Append("VALUES (:COD_MOD_INTL,:NOME_MOD_INTL,:DESC_MOD_INTL,:NOME_ASSBLY_MOD_INTL,:NOME_CLAS_MOD_INTL) ");
                }

                OracleCommand command = this.OracleConnection.CreateCommand();
                command.Transaction = trans;
                command.CommandText = sql.ToString();
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.Add(new OracleParameter("COD_MOD_INTL", inteligenceModule.Id));
                command.Parameters.Add(new OracleParameter("NOME_MOD_INTL", inteligenceModule.Name));
                command.Parameters.Add(new OracleParameter("DESC_MOD_INTL", inteligenceModule.Description));
                command.Parameters.Add(new OracleParameter("NOME_ASSBLY_MOD_INTL", inteligenceModule.Assembly));
                command.Parameters.Add(new OracleParameter("NOME_CLAS_MOD_INTL", inteligenceModule.Class));

                if (command.ExecuteNonQuery() == 0)
                    throw new Exception(string.Format("Não foi possível atualizar o inteligence module. Id:{0} Name:{1}", inteligenceModule.Id, inteligenceModule.Name));

                trans.Commit();
            }
            catch
            {
                if (trans != null)
                    trans.Rollback();

                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        private int GetNextId(OracleTransaction trans)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT MAX(COD_MOD_INTL) FROM TFWDMOD_INTL_GDOR_PROS");

            OracleCommand command = this.OracleConnection.CreateCommand();
            command.Transaction = trans;
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = sql.ToString();

            try
            {
                object ret = command.ExecuteScalar();

                if (ret is DBNull)
                    return 1;
                else
                    return Convert.ToInt32(command.ExecuteScalar()) + 1;
            }
            catch
            {
                throw;
            }
        }

        public void Delete(int inteligenceModuleId)
        {
            StringBuilder sql = new StringBuilder();
            OracleTransaction trans = null;
            try
            {
                this.OracleConnection.Open();
                sql.Append("DELETE FROM TFWDMOD_INTL_GDOR_PROS ");
                sql.Append("WHERE COD_MOD_INTL=:COD_MOD_INTL ");

                OracleCommand command = this.OracleConnection.CreateCommand();
                command.Transaction = trans;
                command.CommandText = sql.ToString();
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.Add(new OracleParameter("COD_MOD_INTL", inteligenceModuleId));

                if (command.ExecuteNonQuery() == 0)
                    throw new Exception(string.Format("Não foi possível excluir o inteligence module. Id:{0}", inteligenceModuleId));
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }
    }
}
